"""
MySQL数据库工具类
"""
import pymysql
import decimal
from pymysql import converters
from typing import List, Dict, Any, Optional, Union
from utils.logger import logger
from config.env_config import env_config


class __DBUtil(object):
    """数据库基础工具类"""

    def __init__(self, host: str, port: int, user: str, passwd: str, database: str = None):
        """
        初始化数据库连接
        
        参数:
            host: 数据库主机地址
            port: 数据库端口
            user: 用户名
            passwd: 密码
            database: 数据库名称
        """
        # 处理mysql select的bit类型字段返回不为\x00
        conversions = converters.conversions.copy()
        conversions[pymysql.FIELD_TYPE.BIT] = lambda x: '1' if x != b'\x00' else '0'
        
        try:
            self.conn = pymysql.connect(
                host=host,
                port=int(port),
                user=user,
                passwd=passwd,
                database=database,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor,
                conv=conversions,
                autocommit=False
            )
            self.cursor = self.conn.cursor()
            logger.info(f"数据库连接成功: {host}:{port}")
        except Exception as e:
            logger.error(f"数据库连接失败: {str(e)}")
            raise

    def __del__(self):
        """析构函数，自动关闭连接"""
        try:
            if hasattr(self, 'cursor') and self.cursor:
                self.cursor.close()
            if hasattr(self, 'conn') and self.conn:
                self.conn.close()
        except Exception:
            pass

    def close(self):
        """手动关闭数据库连接"""
        try:
            if self.cursor:
                self.cursor.close()
            if self.conn:
                self.conn.close()
            logger.info("数据库连接已关闭")
        except Exception as e:
            logger.error(f"数据库连接关闭失败: {str(e)}")

    def _reconnect(self):
        """重新连接数据库"""
        try:
            self.conn.ping(reconnect=True)
            self.cursor = self.conn.cursor()
            logger.info("数据库重连成功")
        except Exception as e:
            logger.error(f"数据库重连失败: {str(e)}")
            raise

    def execute(self, sql: str, args: List = None) -> int:
        """
        执行SQL语句（INSERT, UPDATE, DELETE）
        
        参数:
            sql: SQL语句
            args: 参数列表
        返回:
            affected_rows: 受影响的行数
        """
        if args is None:
            args = []
            
        try:
            affected_rows = self.cursor.execute(sql, args)
            self.conn.commit()
            logger.info(f"执行SQL成功: {sql}, 参数: {args}, 影响行数: {affected_rows}")
            return affected_rows
        except Exception as e:
            logger.error(f"执行SQL失败: {sql}, 参数: {args}, 错误: {str(e)}")
            self.conn.rollback()
            try:
                # 尝试重连
                self._reconnect()
                affected_rows = self.cursor.execute(sql, args)
                self.conn.commit()
                logger.info(f"重连后执行SQL成功: {sql}, 参数: {args}, 影响行数: {affected_rows}")
                return affected_rows
            except Exception as retry_e:
                logger.error(f"重连后执行SQL仍失败: {str(retry_e)}")
                self.conn.rollback()
                raise

    def query(self, sql: str, args: List = None) -> List[Dict[str, Any]]:
        """
        查询数据
        
        参数:
            sql: SQL语句
            args: 参数列表
        返回:
            查询结果列表
        """
        if args is None:
            args = []
            
        try:
            self.cursor.execute(sql, args)
            result = self.cursor.fetchall()
            logger.info(f"查询SQL成功: {sql}, 参数: {args}, 结果数量: {len(result)}")
            return result
        except Exception as e:
            logger.error(f"查询SQL失败: {sql}, 参数: {args}, 错误: {str(e)}")
            try:
                # 尝试重连
                self._reconnect()
                self.cursor.execute(sql, args)
                result = self.cursor.fetchall()
                logger.info(f"重连后查询SQL成功: {sql}, 参数: {args}, 结果数量: {len(result)}")
                return result
            except Exception as retry_e:
                logger.error(f"重连后查询SQL仍失败: {str(retry_e)}")
                raise

    def query_one(self, sql: str, args: List = None) -> Optional[Dict[str, Any]]:
        """
        查询单条记录
        
        参数:
            sql: SQL语句
            args: 参数列表
        返回:
            单条记录或None
        """
        result = self.query(sql, args)
        return result[0] if result else None


class SqlFormat:
    """SQL语句格式化工具类"""
    
    @staticmethod
    def select_normal(table: str, data: str = '*', params: Dict[str, Any] = None, end: str = '') -> List[Union[str, List]]:
        """
        简单单表查询SQL生成
        
        参数:
            table: 表名
            data: 查询字段，默认为*
            params: 查询条件字典
            end: 额外的SQL语句（如ORDER BY, LIMIT等）
        返回:
            [sql语句, 参数列表]
        """
        if params is None:
            params = {}
            
        params_value = []
        sql = f"SELECT {data} FROM {table}"
        
        if params:
            sql += " WHERE"
            conditions = []
            for key, value in params.items():
                conditions.append(f"{key} = %s")
                params_value.append(value)
            sql += " " + " AND ".join(conditions)
            
        if end:
            sql += f" {end}"
            
        return [sql, params_value]

    @staticmethod
    def insert_sql(table: str, data: Dict[str, Any]) -> List[Union[str, List]]:
        """
        生成INSERT SQL语句
        
        参数:
            table: 表名
            data: 插入数据字典
        返回:
            [sql语句, 参数列表]
        """
        if not data:
            raise ValueError("插入数据不能为空")
            
        columns = list(data.keys())
        values = list(data.values())
        placeholders = ', '.join(['%s'] * len(columns))
        
        sql = f"INSERT INTO {table} ({', '.join(columns)}) VALUES ({placeholders})"
        return [sql, values]

    @staticmethod
    def update_sql(table: str, data: Dict[str, Any], where: Dict[str, Any]) -> List[Union[str, List]]:
        """
        生成UPDATE SQL语句
        
        参数:
            table: 表名
            data: 更新数据字典
            where: 条件字典
        返回:
            [sql语句, 参数列表]
        """
        if not data:
            raise ValueError("更新数据不能为空")
        if not where:
            raise ValueError("更新条件不能为空")
            
        set_clauses = []
        params = []
        
        for key, value in data.items():
            set_clauses.append(f"{key} = %s")
            params.append(value)
            
        where_clauses = []
        for key, value in where.items():
            where_clauses.append(f"{key} = %s")
            params.append(value)
            
        sql = f"UPDATE {table} SET {', '.join(set_clauses)} WHERE {' AND '.join(where_clauses)}"
        return [sql, params]


class DBObject(__DBUtil):
    """数据库操作对象"""
    
    def __init__(self, database: str = None):
        """
        初始化数据库连接，从配置文件读取连接信息
        
        参数:
            database: 数据库名称，如果不指定则从配置读取
        """
        try:
            db_config = env_config.get_db_config()
            user = db_config.get("user", "root")
            passwd = db_config.get("password", "")
            host = db_config.get("host", "localhost")
            port = db_config.get("port", 3306)
            db_name = database or db_config.get("database", "test")
            
            super(DBObject, self).__init__(host, port, user, passwd, db_name)
        except Exception as e:
            logger.error(f"数据库初始化失败: {str(e)}")
            raise

    def select_one_table(self, tablename: str, **kwargs) -> List[Dict[str, Any]]:
        """
        单表查询
        
        参数:
            tablename: 表名
            **kwargs: 查询条件
        返回:
            查询结果列表
        """
        sql = f"SELECT * FROM {tablename}"
        param_list = []
        
        if kwargs:
            sql += " WHERE"
            conditions = []
            for key, value in kwargs.items():
                conditions.append(f"{key} = %s")
                param_list.append(value)
            sql += " " + " AND ".join(conditions)
            
        return self.query(sql, param_list)

    def select_one(self, tablename: str, **kwargs) -> Optional[Dict[str, Any]]:
        """
        查询一条记录
        
        参数:
            tablename: 表名
            **kwargs: 查询条件
        返回:
            单条记录或None
        """
        result = self.select_one_table(tablename, **kwargs)
        return result[0] if result else None

    def insert_data(self, table: str, data: Dict[str, Any]) -> int:
        """
        插入数据（改进版本，使用参数化查询）
        
        参数:
            table: 表名
            data: 插入数据字典
        返回:
            受影响的行数
        """
        if not table or not data:
            raise ValueError("table和data都不能为空")
            
        # 过滤None值
        filtered_data = {k: v for k, v in data.items() if v is not None}
        
        if not filtered_data:
            logger.warning("过滤None值后没有有效数据可插入")
            return 0
            
        sql, params = SqlFormat.insert_sql(table, filtered_data)
        return self.execute(sql, params)

    def insert_ignore_data(self, table: str, data: Dict[str, Any]) -> int:
        """
        插入数据（忽略重复）
        
        参数:
            table: 表名
            data: 插入数据字典
        返回:
            受影响的行数
        """
        if not table or not data:
            raise ValueError("table和data都不能为空")
            
        # 过滤None值
        filtered_data = {k: v for k, v in data.items() if v is not None}
        
        if not filtered_data:
            logger.warning("过滤None值后没有有效数据可插入")
            return 0
            
        columns = list(filtered_data.keys())
        values = list(filtered_data.values())
        placeholders = ', '.join(['%s'] * len(columns))
        
        sql = f"INSERT IGNORE INTO {table} ({', '.join(columns)}) VALUES ({placeholders})"
        return self.execute(sql, values)

    def update_data(self, table: str, data: Dict[str, Any], where: Dict[str, Any]) -> int:
        """
        更新数据
        
        参数:
            table: 表名
            data: 更新数据字典
            where: 条件字典
        返回:
            受影响的行数
        """
        sql, params = SqlFormat.update_sql(table, data, where)
        return self.execute(sql, params)

    def delete_data(self, table: str, where: Dict[str, Any]) -> int:
        """
        删除数据
        
        参数:
            table: 表名
            where: 条件字典
        返回:
            受影响的行数
        """
        if not where:
            raise ValueError("删除条件不能为空，防止误删全表数据")
            
        where_clauses = []
        params = []
        
        for key, value in where.items():
            where_clauses.append(f"{key} = %s")
            params.append(value)
            
        sql = f"DELETE FROM {table} WHERE {' AND '.join(where_clauses)}"
        return self.execute(sql, params)

    def execute_transaction(self, sql_list: List[tuple]) -> bool:
        """
        执行事务
        
        参数:
            sql_list: SQL语句和参数的元组列表 [(sql1, params1), (sql2, params2), ...]
        返回:
            是否执行成功
        """
        try:
            for sql, params in sql_list:
                self.cursor.execute(sql, params or [])
            self.conn.commit()
            logger.info(f"事务执行成功，共执行{len(sql_list)}条SQL")
            return True
        except Exception as e:
            self.conn.rollback()
            logger.error(f"事务执行失败，已回滚: {str(e)}")
            return False


# 创建全局数据库对象实例
try:
    db = DBObject()
    logger.info("全局数据库对象创建成功")
except Exception as e:
    logger.error(f"全局数据库对象创建失败: {str(e)}")
    db = None 